/*
 * ApplyScheduleCommand.java
 *
 * Created on October 3, 2007, 12:39 AM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package drb;

import java.sql.*;

/**
 *
 * @author Asger
 */
public class ApplyScheduleCommand extends Command {
    
    /**
     * Creates a new instance of ApplyScheduleCommand
     */
    public ApplyScheduleCommand() {
    }
    
    public int getAvailableTrain(String station, long timeStart, long timeEnd, Connection connection) throws SQLException
    {
        String sql = "SELECT c1.train_number AS train_number FROM connections c1 WHERE NOT EXISTS " +
                "(SELECT * FROM connections c2 WHERE c2.train_number=c1.train_number " +
                "AND c2.departure<"+timeEnd+" AND c2.arrival>"+timeStart+")";
        ResultSet result = connection.createStatement().executeQuery(sql);
        
        if (result.next())
        {
            return result.getInt("train_number");
        }
        
        // No train was found.. create a new train number
        sql = "SELECT MAX(train_number) FROM connections";
        result = connection.createStatement().executeQuery(sql);
        if (!result.next())
            throw new RuntimeException("Could not create a new train number. SQL did not return anything on: " + sql);
        
        // Make a train number one higher than the old one.
        int trainNumber = 1 + result.getInt(1);
        
        return trainNumber;
    }
    
    public int getAvailableWagon(String station, long timeStart, long timeEnd, Connection connection, char wagonType, char classType) throws SQLException
    {
        // Build the SQL statement to find unused wagons in the specified interval
        // Natural join transports with connections to access the time of arrival
        String sql = "SELECT w1.wagon_number AS wagon_number FROM wagons w1 " +
                "WHERE wagon_type='"+wagonType+"' AND " +
                "EXISTS (SELECT * FROM seats s WHERE s.wagon_number=w1.wagon_number AND s.class='"+classType+"') AND " +
                "NOT EXISTS (SELECT * FROM transports t2 NATURAL JOIN connections c2 " +
                    "WHERE t2.wagon_number=w1.wagon_number " +
                    "AND t2.departure<"+timeEnd+" AND c2.arrival>"+timeStart+")";
        ResultSet result = connection.createStatement().executeQuery(sql);
        
        if (result.next())
        {
            return result.getInt("wagon_number");
        }
        
        // No wagon was found.. create a new wagon number
        sql = "SELECT MAX(wagon_number) FROM wagons";
        result = connection.createStatement().executeQuery(sql);
        if (!result.next())
            throw new RuntimeException("Could not create a new wagon number. SQL did not return anything on: " + sql);
        
        // Make a new wagon number one higher than the old one
        int wagonNumber = 1 + result.getInt(1);
        
        // Insert the new wagon in the database.
        sql = "INSERT INTO wagons (wagon_number, wagon_type) VALUES ("+wagonNumber+", '"+wagonType+"')";
        connection.createStatement().execute(sql);
        
        // Create wagons in the new wagon
        int numRows = 10;
        int numCols = 4;
        for (int row=0; row<numRows; row++)
        {
            for (int col=0; col<numCols; col++)
            {
                char pos = (char)('A' + col);
                char placement = (col == 1 || col == 2)? 'A':'W';
                // Add the seat
                sql = "INSERT INTO seats (seat_row, seat_position, class, seat_placement, wagon_number) VALUES " +
                        "("+row+", '"+pos+"', '"+classType+"', '"+placement+"', "+wagonNumber+")";
                connection.createStatement().execute(sql);
                
                // Add the seat as a neighbour to the seat next to it
                if (pos == 'B' || pos == 'D')
                {
                    char npos = (char)(pos-1);
                    sql = "INSERT INTO neighbours (wagon_number, row1, position1, row2, position2) VALUES " +
                            "("+wagonNumber+", "+row+", '"+pos+"', "+row+", '"+npos+"')";
                    connection.createStatement().execute(sql);
                }
            }
        }
        
        return wagonNumber;
    }
    
    public int applyScheduleAtDay(Connection connection, long day) throws SQLException {
        // Find scheduled connections today, that have not already been created.
        String sql = "SELECT * FROM schedule WHERE " +
                "week_days LIKE '%"+getWeekDayName(day)+"%' AND " +
                "(departure + "+day+", to_name, from_name) NOT IN " +
                "(SELECT c.departure, c.to_name, c.from_name FROM connections c) " +
                "ORDER BY schedule_id";
        ResultSet schedule = connection.createStatement().executeQuery(sql);
        
        int numNewSchedules = 0;
        int trainNumber = -1;
        int scheduleId = -1;
        while (schedule.next())
        {
            String toname = schedule.getString("to_name");
            String fromname = schedule.getString("from_name");
            long departure = schedule.getLong("departure") + day;
            long arrival = schedule.getLong("arrival") + day;
            int ecoprice = schedule.getInt("eco_price");
            int fcprice = schedule.getInt("fc_price");
            char wagonType = schedule.getString("wagon_type").charAt(0);
            
            int newid = schedule.getInt("schedule_id");
            if (scheduleId != newid)
            {
                // Find a free train number to assign to this schedule
                trainNumber = getAvailableTrain(fromname, departure, arrival, connection);
                scheduleId = newid;
            }
            
            sql = "INSERT INTO connections (to_name, from_name, departure, arrival, eco_price, fc_price, train_number) VALUES " +
                    "('" + toname +"', '"+ fromname +"', "+ departure +", "+ arrival +", "+ ecoprice +", "+ fcprice + ", "+trainNumber+")";
            
            connection.createStatement().execute(sql);
            
            int ecoWagons = schedule.getInt("eco_wagons");
            int fcWagons = schedule.getInt("fc_wagons");
            int numWagons = ecoWagons + fcWagons;
            
            for (int i=0; i<numWagons; i++)
            {
                char seatClass = (i<ecoWagons)? 'B':'A';
                
                // Find a free wagon number to assign to this connection
                int wagonNumber = getAvailableWagon(fromname, departure, arrival, connection, wagonType, seatClass);
                sql = "INSERT INTO transports (departure, to_name, from_name, wagon_number, train_number) VALUES " +
                        "("+departure+", '"+toname+"', '"+fromname+"', "+wagonNumber+", "+trainNumber+")";
                connection.createStatement().execute(sql);
            }
            
            numNewSchedules++;
        }
        return numNewSchedules;
    }
    
    public void execute() throws SQLException
    {
        long today = getTodayTime();
        
        System.out.print("Applying schedule... ");
        connection.setAutoCommit(false);
        
        int numNewSchedules = 0;
        for (int i=0; i<7; i++) {
            numNewSchedules += applyScheduleAtDay(connection, today + i*ONE_DAY);
        }
        
        connection.commit();
        connection.setAutoCommit(true);
        System.out.println("OK");
        
        //System.out.println("New connections added: " + numNewSchedules);
    }
}
